using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Reflection;

namespace MyWords
{
    class DB
    {
        private static string _connString;
        private static string connectionString
        {
            get
            {
                if (_connString == null)
                {
                    try
                    {
                        _connString = System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
                    }
                    catch
                    {
                        _connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyWords\MyWords.mdb";
                    }
                }
                return _connString;
            }
        }

        ///
        /// Convert any possible string-Value of a given enumeration
        /// type to its internal representation.
        ///
        static object StringToEnum(Type t, string Value)
        {
            foreach (FieldInfo fi in t.GetFields())
                if (fi.Name == Value)
                    return fi.GetValue(null);    // We use null because
            // enumeration values
            // are static

            throw new Exception(string.Format("Can't convert {0} to {1}", Value, t.ToString()));
        }

        public static void LoadAudio(Word myWord)
        {
            if (myWord.Audio == AudioFormat.Unknown)
            {
                myWord.Audio = AudioFormat.None;
                string queryStr;
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    using (OleDbCommand comm = conn.CreateCommand())
                    {
                        int wordID;
                        queryStr = string.Format("select ID from words where head='{0}'", myWord.Head.Replace("'", "''"));
                        comm.CommandText = queryStr;
                        wordID = (int)comm.ExecuteScalar();

                        queryStr = String.Format("select AudioFormat, AudioBytes from words inner join audio on words.ID=audio.wordID where head='{0}'", 
                                        myWord.Head.Replace("'","''"));
                        comm.CommandText = queryStr;
                        using (OleDbDataReader rd = comm.ExecuteReader())
                        {
                            if (rd.Read())
                            {
                                if (rd.IsDBNull(0) || rd.IsDBNull(1)) return;
                                int n = Convert.ToInt32(rd.GetBytes(1, 0, null, 0, 0));
                                if (n == 0) return;
                                byte[] bytes = new byte[n];
                                rd.GetBytes(1, 0, bytes, 0, n);
                                myWord.AudioBytes = bytes;
                                try
                                {
                                    myWord.Audio = (AudioFormat) StringToEnum(typeof (AudioFormat), rd.GetString(0));
                                }
                                catch
                                {
                                    return;
                                }
                            }
                        }
                    }
                }
                UpdateWordSchedule(myWord);
            }
        }

        public static void SaveAudio(Word myWord)
        {
            if (myWord.Audio == AudioFormat.Unknown || myWord.Audio == AudioFormat.None || myWord.AudioBytes == null) return;

            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    int wordID;
                    queryStr = string.Format("select ID from words where head='{0}'", myWord.Head.Replace("'", "''"));
                    comm.CommandText = queryStr;
                    wordID = (int)comm.ExecuteScalar();

                    comm.CommandText = "delete from audio where WordID=@WordID";
                    comm.Parameters.Add(new OleDbParameter("@WordID", wordID));
                    comm.ExecuteNonQuery();

                    comm.CommandText = "insert into audio (WordID, AudioFormat, AudioBytes) values (@WordID, @Audio, @AudioBytes)";
                    comm.Parameters.Clear();
                    comm.Parameters.Add(new OleDbParameter("@WordID", wordID));
                    comm.Parameters.Add(new OleDbParameter("@Audio", myWord.Audio.ToString()));
                    comm.Parameters.Add(new OleDbParameter("@AudioBytes", myWord.AudioBytes));
                    comm.ExecuteNonQuery();
                }
            }

        }

        private static void AddNewWord(Word myWord)
        {
            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr = string.Format("insert into words (head, meaning, createdDate) "
                        + "values ('{0}', '{1}', '{2:yyyy-MM-dd HH:mm:ss}')",
                        myWord.Head.Replace("'", "''"), myWord.Meaning.Replace("'", "''"), myWord.CreatedDate
                        );
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();

                    int wordID;
                    queryStr = string.Format("select ID from words where head='{0}'", myWord.Head.Replace("'", "''"));
                    comm.CommandText = queryStr;
                    wordID = (int) comm.ExecuteScalar();
                }
            }
            UpdateWordSchedule(myWord);
            SaveAudio(myWord);
        }

        private static void UpdateWordSchedule(Word myWord)
        {
            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr = string.Format("select ID from Words where head='{0}'", myWord.Head.Replace("'", "''"));
                    comm.CommandText = queryStr;
                    int WordID = Convert.ToInt32(comm.ExecuteScalar());

                    queryStr = string.Format("delete from Schedule where WordID={0}", WordID);
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();

                    foreach (Schedule aSchedule in myWord.Schedules)
                    {
                        queryStr = "Insert into Schedule (WordID, ScheduleDate, StudyDate) values (@WordID, @ScheduleDate, @StudyDate)";
                        comm.CommandText = queryStr;
                        comm.Parameters.Clear();

                        comm.Parameters.Add(new System.Data.OleDb.OleDbParameter("@WordID", WordID));

                        System.Data.OleDb.OleDbParameter PScheduleDate = new OleDbParameter("@ScheduleDate", OleDbType.Date);
                        PScheduleDate.Value = aSchedule.ScheduleDate.Value;
                        comm.Parameters.Add(PScheduleDate);

                        System.Data.OleDb.OleDbParameter PStudyDate = new OleDbParameter("@StudyDate", OleDbType.Date);
                        if (aSchedule.StudyDate.IsNull)
                            PStudyDate.Value = DBNull.Value;
                        else
                            PStudyDate.Value = aSchedule.StudyDate.Value;
                        comm.Parameters.Add(PStudyDate);

                        comm.ExecuteNonQuery();
                    }
                }
            }
        }

        private static void UpdateWord(Word myWord)
        {
            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr = string.Format("update words set meaning='{1}' where head='{0}' ",
                        myWord.Head.Replace("'", "''"), myWord.Meaning.Replace("'", "''")
                        );
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();
                }
            }
            SaveAudio(myWord);
            UpdateWordSchedule(myWord);
        }

        public static void UpdateWordHeadMeaning(Word myWord, string oldHead)
        {
            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr = string.Format("update words set head='{0}', meaning='{1}' where head='{2}' ",
                        myWord.Head.Replace("'", "''"), myWord.Meaning.Replace("'", "''"), oldHead.Replace("'", "''")
                        );
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();
                }
            }
        }

        public static bool ExistWord(Word myWord)
        {
            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr = string.Format("select head from words where head='{0}' ", myWord.Head.Replace("'", "''"));
                    comm.CommandText = queryStr;
                    object head = comm.ExecuteScalar();
                    if (head != null)
                        return true;
                    else
                        return false;
                }
            }
        }

        //add new or update
        public static void SaveWord(Word myWord)
        {
            if (ExistWord(myWord))
            {
                UpdateWord(myWord);
            }
            else
            {
                AddNewWord(myWord);
            }
        }

        public static Word LoadWord(string head)
        {
            Word myWord;

            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr = "select head, meaning, createdDate "
                        + "from words where head='" + head.Replace("'", "''") + "' ";
                    comm.CommandText = queryStr;
                    using (OleDbDataReader rd = comm.ExecuteReader())
                    {
                        if (!rd.HasRows) return null;
                        rd.Read();
                        myWord = new Word(
                                rd.GetString(0),
                                rd.GetString(1),
                                rd.GetDateTime(2)
                            );
                    }

                    queryStr = string.Format("select ID from Words where head='{0}'", myWord.Head.Replace("'", "''"));
                    comm.CommandText = queryStr;
                    int WordID = Convert.ToInt32(comm.ExecuteScalar());

                    if (myWord.Audio != AudioFormat.Unknown && myWord.Audio != AudioFormat.None && myWord.AudioBytes != null)
                    {
                        queryStr = "select ScheduleDate, StudyDate from Schedule where WordID=" + WordID.ToString() + " order by ScheduleDate";
                        comm.CommandText = queryStr;
                        using (OleDbDataReader rd = comm.ExecuteReader())
                        {
                            while (rd.Read())
                            {
                                if (rd.IsDBNull(1))
                                    myWord.Schedules.Add(new Schedule(rd.GetDateTime(0)));
                                else
                                    myWord.Schedules.Add(new Schedule(rd.GetDateTime(0), rd.GetDateTime(1)));
                            }
                        }
                    }
                }
            }
            return myWord;
        }

        public static WordList LoadWords(DateTime dateTime1, DateTime dateTime2)
        {
            WordList wordList = new WordList();

            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr =
                        " select head, meaning, createdDate, ID "
                        + " from Words "
                        + " where createdDate between @dt1 and @dt2 "
                        + " order by createdDate ";
                    comm.CommandText = queryStr;
                    comm.Parameters.Clear();
                    comm.Parameters.Add(new OleDbParameter("@dt1", dateTime1));
                    comm.Parameters.Add(new OleDbParameter("@dt2", dateTime2));
                    using (OleDbDataReader rd0 = comm.ExecuteReader())
                    {
                        using (OleDbCommand Comm1 = conn.CreateCommand())
                        {
                            while (rd0.Read())
                            {
                                Word myWord = new Word(
                                        rd0.GetString(0),
                                        rd0.GetString(1),
                                        rd0.GetDateTime(2)
                                    );

                                int WordID = rd0.GetInt32(3);
                                queryStr = "select ScheduleDate, StudyDate from Schedule where WordID=" + WordID.ToString() + " order by ScheduleDate";
                                Comm1.CommandText = queryStr;
                                using (OleDbDataReader rd1 = Comm1.ExecuteReader())
                                {
                                    while (rd1.Read())
                                    {
                                        if (rd1.IsDBNull(1))
                                            myWord.Schedules.Add(new Schedule(rd1.GetDateTime(0)));
                                        else
                                            myWord.Schedules.Add(new Schedule(rd1.GetDateTime(0), rd1.GetDateTime(1)));
                                    }
                                }
                                wordList.Add(myWord);
                            }
                        }
                    }
                }
            }

            return wordList;
        }

        public static WordList LoadScheduledWords(DateTime dateTime)
        {
            WordList wordList = new WordList();

            string queryStr;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    queryStr =
                        " select head, meaning, scheduleDate, WordID, createdDate "
                        + " from Words W inner join "
                        + " (select WordID, min(ScheduleDate) as scheduleDate from Schedule where ScheduleDate=@dt or (ScheduleDate<@dt and StudyDate is null) group by WordID) S "
                        + " on W.ID=S.WordID "
                        + " order by scheduleDate, createdDate ";
                    comm.CommandText = queryStr;
                    comm.Parameters.Clear();
                    comm.Parameters.Add(new OleDbParameter("@dt", dateTime.Date));
                    using (OleDbDataReader rd0 = comm.ExecuteReader())
                    {
                        using (OleDbCommand Comm1 = conn.CreateCommand())
                        {
                            while (rd0.Read())
                            {
                                Word myWord = new Word(
                                        rd0.GetString(0),
                                        rd0.GetString(1),
                                        rd0.GetDateTime(4)
                                    );

                                int WordID = rd0.GetInt32(3);
                                queryStr = "select ScheduleDate, StudyDate from Schedule where WordID=" + WordID.ToString() + " order by ScheduleDate";
                                Comm1.CommandText = queryStr;
                                using (OleDbDataReader rd1 = Comm1.ExecuteReader())
                                {
                                    while (rd1.Read())
                                    {
                                        if (rd1.IsDBNull(1))
                                            myWord.Schedules.Add(new Schedule(rd1.GetDateTime(0)));
                                        else
                                            myWord.Schedules.Add(new Schedule(rd1.GetDateTime(0), rd1.GetDateTime(1)));
                                    }
                                }
                                wordList.Add(myWord);
                            }
                        }
                    }
                }
            }

            return wordList;
        }

        public static void ExecQuery(string p)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    comm.CommandText = p;
                    try
                    {
                        comm.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.ToString());
                    }
                }
            }            


        }

        public static System.Data.DataSet ExecDataSet(string p)
        {
            DataSet ds = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    try
                    {
                        comm.CommandText = p;
                        OleDbDataAdapter adp = new OleDbDataAdapter(comm);
                        adp.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.ToString());
                    }
                }
            }

            return ds;
        }


        public static List<DateTime> LoadAvailableAddedDateList()
        {
            List<DateTime> aList = new List<DateTime>();
            DataSet ds = ExecDataSet("select distinct createdDate from words order by createdDate desc");
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                DateTime dt = (DateTime)r[0];
                dt = dt.Date;
                if (!aList.Contains(dt))
                {
                    aList.Add(dt);
                }
            }
            return aList;
        }

        public static List<DateTime> LoadAvailableScheduledDateList()
        {
            List<DateTime> aList = new List<DateTime>();
            DataSet ds = ExecDataSet("select distinct scheduleDate from schedule order by scheduleDate desc");
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                DateTime dt = (DateTime)r[0];
                dt = dt.Date;
                if (dt<=DateTime.Today.AddDays(7) && !aList.Contains(dt))
                {
                    aList.Add(dt);
                }
            }
            return aList;
        }

        public static void DeleteWord(string head)
        {
            if (string.IsNullOrEmpty(head)) return;

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand comm = conn.CreateCommand())
                {
                    string queryStr = string.Format("select ID from Words where head='{0}'", head.Replace("'", "''"));
                    comm.CommandText = queryStr;
                    object obj = comm.ExecuteScalar();
                    if (obj == null) return;

                    int WordID = Convert.ToInt32(obj);

                    queryStr = string.Format("delete from Schedule where WordID={0}", WordID);
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();

                    queryStr = string.Format("delete from audio where WordID={0}", WordID);
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();

                    queryStr = string.Format("delete from Words where ID={0}", WordID);
                    comm.CommandText = queryStr;
                    comm.ExecuteNonQuery();
                }
            }
        }
    }
}
